﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmStaffProfilesNew

    Private ImageUrl As String = ""

    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombBranch.Items.Add(Branch)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ClearNewProfile()
        Me.txtNameEn.Clear()
        Me.txtNameAr.Clear()
        Me.ComboMaritalStatus.SelectedIndex = -1
        Me.CombNationalityEn.SelectedItem = "Saudi"
        Me.combReligionEn.SelectedIndex = -1
        Me.CombGenderEn.SelectedIndex = -1
        Me.DTPBirth.Value = Now
        Me.txtMobile.Clear()
        Me.txtEmail.Clear()
        Me.txtAddress.Clear()
        Me.txtGOSINo.Clear()
        Me.txtSponsor.Text = "Broker Vision"

        Me.PictureBox1.Image = My.Resources.default_profile
        ImageUrl = ""

        Me.txtPassportNo.Clear()
        Me.txtPssprtIsuPlce.Clear()
        Me.txtPssprtIsuPlceAr.Clear()
        Me.DTPasspIssueDate.Value = Now
        Me.DTPasspExpireDate.Value = Now

        Me.txtIDNo.Clear()
        Me.txtIDIssuePlace.Clear()
        Me.txtIDIssuePlaceAr.Clear()
        Me.DTPIDIssueDate.Value = Now
        Me.DTPIDExpireDate.Value = Now

        Me.CombJobTitle.SelectedIndex = -1
        Me.DTHireDate.Value = Now
        Me.nudVacationEntilement.Value = nudVacationEntilement.Minimum
        Me.CombBranch.SelectedIndex = -1

        Me.txtDoctorName.Clear()
        Me.txtDoctorAddress.Clear()
        Me.txtDoctorPhoneNo.Clear()
        Me.CombBloodType.SelectedIndex = -1
        Me.txtMedicalConditions.Clear()
        Me.txtAllergies.Clear()
        Me.txtCurrentMedications.Clear()

        Me.txtEmergContactName.Clear()
        Me.txtEmergContactAddress.Clear()
        Me.txtEmergContactPhone.Clear()
        Me.txtEmergContactRelationship.Clear()

        Me.txtBS.Value = 0
        Me.txtHA.Value = 0
        Me.txtTA.Value = 0
        Me.txtFuel.Value = 0
        Me.txtSupervision.Value = 0
        Me.txtTuitionFess.Value = 0
        Me.txtMobileAllowance.Value = 0
        Me.txtOthers.Value = 0
    End Sub

    Sub FillPositionsList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Position", cnn)
            Dim Reader As SqlDataReader

            Me.CombJobTitle.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.CombJobTitle.Items.Add(Reader.Item("Position"))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillNationalityList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Nationality", cnn)
            Dim Reader As SqlDataReader

            Me.CombNationalityEn.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.CombNationalityEn.Items.Add(Reader.Item("NationalityEn"))
            End While
            cnn.Close()

            Me.CombNationalityEn.SelectedItem = "Saudi"

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillReligionList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From Religion", cnn)
            Dim Reader As SqlDataReader

            Me.combReligionEn.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.combReligionEn.Items.Add(Reader.Item("ReligionEn"))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()
        If Me.txtNameEn.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtNameEn, "Please fill in all details")
            Me.txtNameEn.Focus()

        ElseIf Me.txtNameAr.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtNameAr, "Please fill in all details")
            Me.txtNameAr.Focus()

        ElseIf Me.ComboMaritalStatus.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.ComboMaritalStatus, "Please fill in all details")
            Me.ComboMaritalStatus.Focus()

        ElseIf Me.CombNationalityEn.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombNationalityEn, "Please fill in all details")
            Me.CombNationalityEn.Focus()

        ElseIf Me.combReligionEn.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.combReligionEn, "Please fill in all details")
            Me.combReligionEn.Focus()

        ElseIf Me.CombGenderEn.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombGenderEn, "Please fill in all details")
            Me.CombGenderEn.Focus()

        ElseIf Me.txtMobile.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtMobile, "Please fill in all details")
            Me.txtMobile.Focus()

        ElseIf Me.txtAddress.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtAddress, "Please fill in all details")
            Me.txtAddress.Focus()

        ElseIf Me.txtGOSINo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtGOSINo, "Please fill in all details")
            Me.txtGOSINo.Focus()

        ElseIf Me.txtSponsor.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtSponsor, "Please fill in all details")
            Me.txtSponsor.Focus()

        ElseIf Me.txtPassportNo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtPassportNo, "Please fill in all details")
            Me.txtPassportNo.Focus()

        ElseIf Me.txtPssprtIsuPlce.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtPssprtIsuPlce, "Please fill in all details")
            Me.txtPssprtIsuPlce.Focus()

        ElseIf Me.txtPssprtIsuPlceAr.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtPssprtIsuPlceAr, "Please fill in all details")
            Me.txtPssprtIsuPlceAr.Focus()

        ElseIf Me.txtIDNo.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtIDNo, "Please fill in all details")
            Me.txtIDNo.Focus()

        ElseIf Me.txtIDIssuePlace.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtIDIssuePlace, "Please fill in all details")
            Me.txtIDIssuePlace.Focus()

        ElseIf Me.txtIDIssuePlaceAr.Text.Trim.Length = 0 Then
            Me.ErrProv.SetError(Me.txtIDIssuePlaceAr, "Please fill in all details")
            Me.txtIDIssuePlaceAr.Focus()

        ElseIf Me.CombJobTitle.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombJobTitle, "Please fill in all details")
            Me.CombJobTitle.Focus()

        ElseIf Me.CombBranch.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombBranch, "Please fill in all details")
            Me.CombBranch.Focus()

        ElseIf Not Me.txtEmail.Text.Trim.Length = 0 And _
            Not System.Text.RegularExpressions.Regex.IsMatch(Me.txtEmail.Text.Trim, "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$") Then
            MsgBox("Please enter a valid email address or leave blank")
            Exit Sub
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim Photo, atPhoto As String
                Dim SNo As Integer

                If ImageUrl <> "" Then
                    Photo = " ,Photo "
                    atPhoto = " ,@Photo "
                Else
                    Photo = ""
                    atPhoto = ""
                End If

                Dim cmd As New SqlCommand("Insert Into StaffProfiles " & _
                                          "(FullNameEn,FullNameAr,Nationality,Gender,Email,Mobile,MaritalStatus,DateofBirth,Address,GOSINo,Sponsor,Position,HireDate,VacationPeriod," & _
                                          "Branch,IDNo,IDExDate,IDIssuePlace,IDIssuePlaceAr,IDIssueDate,PassportNo,ExDate,PassIssuePlace,PassIssuePlaceAr,PassIssueDate,ReligionEn," & _
                                          "DoctorName,DoctorAddress,DoctorPhoneNo,BloodType,MedicalConditions,Allergies,CurrentMedications," & _
                                          "EmergContactName,EmergContactRelationship,EmergContactAddress,EmergContactPhone," & _
                                          "SalaryBS,SalaryHA,SalaryTA,SalaryFuel,SalarySupervision,SalaryTuitionFees,SalaryMobile,Others,TotalSalary " & Photo & ") " & _
                                          "VALUES " & _
                                          "(@FullNameEn,@FullNameAr,@Nationality,@Gender,@Email,@Mobile,@MaritalStatus,@DateofBirth,@Address,@GOSINo,@Sponsor,@Position,@HireDate,@VacationPeriod," & _
                                          "@Branch,@IDNo,@IDExDate,@IDIssuePlace,@IDIssuePlaceAr,@IDIssueDate,@PassportNo,@ExDate,@PassIssuePlace,@PassIssuePlaceAr,@PassIssueDate,@ReligionEn," & _
                                          "@DoctorName,@DoctorAddress,@DoctorPhoneNo,@BloodType,@MedicalConditions,@Allergies,@CurrentMedications," & _
                                          "@EmergContactName,@EmergContactRelationship,@EmergContactAddress,@EmergContactPhone," & _
                                          "@SalaryBS,@SalaryHA,@SalaryTA,@SalaryFuel,@SalarySupervision,@SalaryTuitionFees,@SalaryMobile,@Others,@TotalSalary " & atPhoto & ") Select SCOPE_IDENTITY()", cnn)

                cmd.Parameters.AddWithValue("@FullNameAr", Me.txtNameAr.Text.Trim)
                cmd.Parameters.AddWithValue("@FullNameEn", Me.txtNameEn.Text.Trim)
                cmd.Parameters.AddWithValue("@MaritalStatus", Me.ComboMaritalStatus.Text)
                cmd.Parameters.AddWithValue("@Nationality", Me.CombNationalityEn.Text)
                cmd.Parameters.AddWithValue("@ReligionEn", Me.combReligionEn.Text)
                cmd.Parameters.AddWithValue("@Gender", Me.CombGenderEn.Text)
                cmd.Parameters.AddWithValue("@DateofBirth", Me.DTPBirth.Value)
                cmd.Parameters.AddWithValue("@Mobile", Me.txtMobile.Text.Trim)
                cmd.Parameters.AddWithValue("@Email", Me.txtEmail.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@GOSINo", Me.txtGOSINo.Text.Trim)
                cmd.Parameters.AddWithValue("@Sponsor", Me.txtSponsor.Text.Trim)

                cmd.Parameters.AddWithValue("@IDNo", Me.txtIDNo.Text.Trim)
                cmd.Parameters.AddWithValue("@IDIssuePlace", Me.txtIDIssuePlace.Text.Trim)
                cmd.Parameters.AddWithValue("@IDIssuePlaceAr", Me.txtIDIssuePlaceAr.Text.Trim)
                cmd.Parameters.AddWithValue("@IDIssueDate", Me.DTPIDIssueDate.Value)
                cmd.Parameters.AddWithValue("@IDExDate", Me.DTPIDExpireDate.Value)

                cmd.Parameters.AddWithValue("@PassportNo", Me.txtPassportNo.Text.Trim)
                cmd.Parameters.AddWithValue("@PassIssuePlace", Me.txtPssprtIsuPlce.Text.Trim)
                cmd.Parameters.AddWithValue("@PassIssuePlaceAr", Me.txtPssprtIsuPlceAr.Text.Trim)
                cmd.Parameters.AddWithValue("@PassIssueDate", Me.DTPasspIssueDate.Value)
                cmd.Parameters.AddWithValue("@ExDate", Me.DTPasspExpireDate.Value)

                cmd.Parameters.AddWithValue("@Position", Me.CombJobTitle.Text)
                cmd.Parameters.AddWithValue("@HireDate", Me.DTHireDate.Value)
                cmd.Parameters.AddWithValue("@VacationPeriod", Me.nudVacationEntilement.Value)
                cmd.Parameters.AddWithValue("@Branch", Me.CombBranch.Text)

                cmd.Parameters.AddWithValue("@DoctorName", Me.txtDoctorName.Text)
                cmd.Parameters.AddWithValue("@DoctorAddress", Me.txtDoctorAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@DoctorPhoneNo", Me.txtDoctorPhoneNo.Text.Trim)
                cmd.Parameters.AddWithValue("@BloodType", Me.CombBloodType.Text.Trim)
                cmd.Parameters.AddWithValue("@MedicalConditions", Me.txtMedicalConditions.Text.Trim)
                cmd.Parameters.AddWithValue("@Allergies", Me.txtAllergies.Text.Trim)
                cmd.Parameters.AddWithValue("@CurrentMedications", Me.txtCurrentMedications.Text.Trim)

                cmd.Parameters.AddWithValue("@EmergContactName", Me.txtEmergContactName.Text)
                cmd.Parameters.AddWithValue("@EmergContactRelationship", Me.txtEmergContactRelationship.Text.Trim)
                cmd.Parameters.AddWithValue("@EmergContactAddress", Me.txtEmergContactAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@EmergContactPhone", Me.txtEmergContactPhone.Text.Trim)

                cmd.Parameters.AddWithValue("@SalaryBS", Me.txtBS.Value)
                cmd.Parameters.AddWithValue("@SalaryHA", Me.txtHA.Value)
                cmd.Parameters.AddWithValue("@SalaryTA", Me.txtTA.Value)
                cmd.Parameters.AddWithValue("@SalaryFuel", Me.txtFuel.Value)
                cmd.Parameters.AddWithValue("@SalarySupervision", Me.txtSupervision.Value)
                cmd.Parameters.AddWithValue("@SalaryTuitionFees", Me.txtTuitionFess.Value)
                cmd.Parameters.AddWithValue("@SalaryMobile", Me.txtMobileAllowance.Value)
                cmd.Parameters.AddWithValue("@Others", Me.txtOthers.Value)
                cmd.Parameters.AddWithValue("@TotalSalary", CDbl(Me.txtTotalSalary.Text.Trim))

                If ImageUrl <> "" Then
                    cmd.Parameters.AddWithValue("@Photo", binarize(ImageUrl))
                    ImageUrl = ""
                End If

                cnn.Open()
                SNo = CInt(cmd.ExecuteScalar)
                cnn.Close()

                MsgBox("Saved Successfully")

                'Create directory to store files
                Dim Dir As New DirectoryInfo(FTPAddress + "Staff")
                Dir.CreateSubdirectory("S-" & SNo)

                ClearNewProfile()

                PrintStaffProfile(SNo)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub choose_photo()
        Dim opf As New OpenFileDialog
        opf.Filter = "Image File (*.jpg,*.bmp,*.png)|*.jpg;*.bmp;*.png|All files (*.*)|*.*"
        opf.FilterIndex = 1
        opf.Title = "Choose employee photo"
        If opf.ShowDialog() <> Windows.Forms.DialogResult.Cancel Then
            ImageUrl = opf.FileName
            PictureBox1.Image = Image.FromFile(ImageUrl)
        End If
    End Sub

    Private Function binarize(ByVal PicPath As String) As Byte()
        Dim fstream As FileStream
        Dim imgdata As Byte()
        Dim data As Byte()
        Dim finfo As FileInfo
        finfo = New FileInfo(PicPath)
        Dim numbyte As Long
        Dim br As BinaryReader
        numbyte = finfo.Length
        fstream = New FileStream(PicPath, FileMode.Open, FileAccess.Read)
        br = New BinaryReader(fstream)
        data = br.ReadBytes(numbyte)
        imgdata = data
        Return imgdata
        Try

        Catch ex As Exception

        End Try
    End Function

    Private Sub frmNewProfile_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        FillPositionsList()
        FillNationalityList()
        FillReligionList()
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        ClearNewProfile()
    End Sub

    Private Sub ButClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButClose.Click
        Me.Close()
    End Sub

    Private Sub CalcSalary()
        txtTotalSalary.Text = CDbl(Me.txtBS.Value + Me.txtHA.Value + Me.txtTA.Value + Me.txtFuel.Value + Me.txtSupervision.Value + _
                                   Me.txtTuitionFess.Value + Me.txtMobileAllowance.Value + Me.txtOthers.Value).ToString("N2")
    End Sub

    Private Sub nudBS_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtBS.ValueChanged
        Me.txtHA.Value = Me.txtBS.Value * 25 / 100
        Me.txtTA.Value = Me.txtBS.Value * 10 / 100

        CalcSalary()
    End Sub

    Private Sub nudHA_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtHA.ValueChanged
        CalcSalary()
    End Sub

    Private Sub nudTA_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTA.ValueChanged
        CalcSalary()
    End Sub

    Private Sub nudMobile_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtMobileAllowance.ValueChanged
        CalcSalary()
    End Sub

    Private Sub nudTeamManagement_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtOthers.ValueChanged
        CalcSalary()
    End Sub

    Private Sub btnBrows_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrows.Click
        choose_photo()
    End Sub

    Private Sub txtSupervision_ValueChanged(sender As System.Object, e As System.EventArgs) Handles txtSupervision.ValueChanged
        CalcSalary()
    End Sub

    Private Sub txtFuel_ValueChanged(sender As System.Object, e As System.EventArgs) Handles txtFuel.ValueChanged
        CalcSalary()
    End Sub

    Private Sub txtTuitionFess_ValueChanged(sender As System.Object, e As System.EventArgs) Handles txtTuitionFess.ValueChanged
        CalcSalary()
    End Sub
End Class